package com.dao;

import com.po.Comment;
import com.po.Note;
import com.utils.JDBCUnits;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NoteDao {

    /**
     * 创建一个有引用的纸条
     * @param note_content 纸条的内容
     * @param envelope_id 信封的编号
     * @param note_name 纸条的名字
     * @param noteQuote 纸条的引用
     */
    public static void createQuoteNote(String note_content, int envelope_id ,String note_name ,String noteQuote){
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = JDBCUnits.getConnectino();
            String sql = "insert into note(note_content,envelope_id,note_name,quote,create_time) values(?,?,?,?,now())";
            ps = c.prepareStatement(sql);
            ps.setString(1, note_content);
            ps.setInt(2, envelope_id);
            ps.setString(3, note_name);
            ps.setString(4,noteQuote);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps);
        }
    }

    /**
     * 获取某个信封的全部的纸条
     * @param envelope_id 信封的id
     * @return 全部纸条的信息
     */
    public static List<Note> getNotes(int envelope_id){
        List<Note> notes = new ArrayList<Note>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "select * from note where envelope_id=?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,envelope_id);
            rs = ps.executeQuery();
            while(rs.next()){
                String note_content = rs.getString("note_content");
                int getPraise = rs.getInt("getPraise");
                int note_id = rs.getInt("note_id");
                String note_name = rs.getString("note_name");
                java.sql.Date create_time = rs.getDate("create_time");
                String quote;
                if(rs.getString("quote") == null){
                    quote = "无";
                }else{
                    quote = rs.getString("quote");
                }
                Note note = new Note(note_name, getPraise, note_content,note_id ,create_time,quote);
                notes.add(note);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUnits.close(c,ps,rs);
        }

        return notes;
    }

    /**
     * 根据纸条的编号获取用户的编号
     * @param note_id
     * @return 用户的编号
     */
    public static int getUserId(int note_id){
        int user_id = 0;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "select t.id as id from t_usr t join envelope e join note n on n.envelope_id = e.envelope_id and e.user_id = t.id and n.note_id = ?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,note_id);
            rs = ps.executeQuery();
            if(rs.next()){
                user_id = rs.getInt("id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnits.close(c,ps,rs);
        }

        return user_id;
    }

    /**
     * 获取有引用的纸条的评论
     * @param user_name
     * @param note_name
     * @return
     */
    public static List<Comment> getQuoteNoteComment(String user_name ,String note_name){
        List<Comment> quoteNoteComment = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "select * from note where note_name =?";
            ps = c.prepareStatement(sql);
            ps.setString(1,note_name);
            rs = ps.executeQuery();
            while(rs.next()){
                if(UserDao.getUser_id(user_name) == UserDao.getID(rs.getInt("envelope_id"))){
                    return CommentDao.getComment(rs.getInt("note_id"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnits.close(c,ps,rs);
        }
        return quoteNoteComment;
    }

    /**
     * 获取纸条的内容
     * @param user_name
     * @param note_name
     * @return
     */
    public static String getContent(String user_name ,String note_name){
        String content = null;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "select * from note where note_name =?";
            ps = c.prepareStatement(sql);
            ps.setString(1,note_name);
            rs = ps.executeQuery();
            while(rs.next()){
                if(UserDao.getUser_id(user_name) == UserDao.getID(rs.getInt("envelope_id"))){
                    content = rs.getString("note_content");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnits.close(c,ps,rs);
        }
        return content;
    }

    /**
     * 删除纸条
     * @param note_id
     */
    public static void deleteNote(int note_id){
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = JDBCUnits.getConnectino();
            String sql = "delete from note where note_id = ?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,note_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnits.close(c,ps);
        }
    }

    /**
     * 删除那些用户喜欢的纸条
     * @param note_id
     */
    public static void deleteLikeNote(int note_id){
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = JDBCUnits.getConnectino();
            String sql = "delete from likesystem where note_id = ?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,note_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnits.close(c,ps);
        }
    }

    /**
     * 创建纸条
     * @param note_content
     * @param envelope_id
     * @param note_name
     * @return
     */
    public static int createNote(String note_content,int envelope_id, String note_name){
        int count = 0;
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = JDBCUnits.getConnectino();
            String sql = "insert into note(note_content,envelope_id,note_name,create_time) values(?,?,?,now())";
            ps = c.prepareStatement(sql);
            ps.setString(1, note_content);
            ps.setInt(2, envelope_id);
            ps.setString(3, note_name);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps);
        }

        return count;
    }

    /**
     * 点赞
     * @param note_id
     */
    public static void LikeNote(int note_id){
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = JDBCUnits.getConnectino();
            String sql = "update note set getPraise = getPraise + 1 where note_id=?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,note_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps);
        }
    }

    /**
     * 取消点赞
     * @param note_id
     */
    public static void DislikeNote(int note_id){
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = JDBCUnits.getConnectino();
            String sql = "update note set getPraise = getPraise - 1 where note_id=?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,note_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps);
        }
    }
}
